package com.deuwise.system.service.impl;

import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.mapper.EntityWrapper;
import com.baomidou.mybatisplus.mapper.Wrapper;
import com.deuwise.common.utils.FileHelperUtils;
import com.deuwise.common.utils.UploadProperties;
import com.deuwise.common.utils.sqlhelper.SqlUtils;
import com.deuwise.system.entity.BackUpDo;
import com.deuwise.system.mapper.BackUpDoMapper;
import com.deuwise.system.service.BackUpDoService;
import com.baomidou.mybatisplus.service.impl.ServiceImpl;
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.http.HttpStatus;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.sql.*;
import java.text.Format;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.concurrent.locks.ReentrantLock;

/**
 * 备份 接口实现类
 *
 * @author deuwise
 * @date 2018-11-14 11:26:52
 **/
@Service
public class BackUpDoServiceImpl extends ServiceImpl<BackUpDoMapper, BackUpDo> implements BackUpDoService {

    private ReentrantLock lock = new ReentrantLock();
    Logger logger = LoggerFactory.getLogger(BackUpDoServiceImpl.class);
    @Value("${spring.datasource.driverClassName}")
    private String jdbcDriver;

    @Value("${spring.datasource.druid.first.url}")
    private String jdbcUrl;
    @Resource
    private UploadProperties uploadProperties;

    @Value("${spring.datasource.druid.first.username}")
    private String jdbcUserName;

    @Value("${spring.datasource.druid.first.password}")
    private String jdbcPassword;
    private String orcaleSQL = "SELECT DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, U.object_name), U.OBJECT_TYPE "
            + "FROM USER_OBJECTS U "
            + "where U.OBJECT_TYPE = 'TABLE' "
            + "or U.OBJECT_TYPE = 'VIEW' "
            + "or U.OBJECT_TYPE = 'INDEX' "
            + "or U.OBJECT_TYPE = 'PROCEDURE' "
            + "or U.OBJECT_TYPE = 'SEQUENCE' "
            + "or U.OBJECT_TYPE = 'TRIGGER' " + "order by U.OBJECT_TYPE desc";


    @Override
    public String backupData(HttpServletRequest request) throws IOException, ClassNotFoundException, SQLException {
        lock.lock();
        try {
            File file = new File(uploadProperties.getBasePath());
            if (!file.exists()) {
                file.mkdir();
            }
            Format f = new SimpleDateFormat("yyyyMMddHmmss");
            Date date = new Date();
            //oracle备份模式
            Integer totalCount=0;
            Integer curcount=0;
            if (jdbcDriver.contains("oracle")) {
                String tablesFile = "";
                String sequencesFile = "";
                String indexsFile = "";
                String createTableStr = f.format(date) + "oracle创表.sql";
                String dataStr = f.format(date) + "oracle数据" + ".sql";
                File dataFile = null;
                StringBuilder fwT = new StringBuilder();
                StringBuilder fwS = new StringBuilder();
                StringBuilder fwI = new StringBuilder();
                File outFile = null;
                Class.forName(jdbcDriver);
                Connection con = DriverManager.getConnection(jdbcUrl, jdbcUserName, jdbcPassword);
                Statement statement = con.createStatement();
                ResultSet rs = statement.executeQuery(orcaleSQL);
                outFile = new File(uploadProperties.getBasePath() + createTableStr);
                if (!outFile.exists()) {
                    /* 创建文件夹*/
                    outFile.createNewFile();
                }
                FileWriter fw = new FileWriter(outFile, true);



                while (rs.next()) {
                    Clob ddl = rs.getClob(1);
                    String objectName = rs.getString(2);

                    String ddlStr = ddl.getSubString(1L, (int) ddl.length());
                    if ("TABLE".equals(objectName)) {
                        fwT.append(ddlStr).append("\r\n");

                    }
                    if ("SEQUENCE".equals(objectName)) {
                        fwS.append(ddlStr).append("\r\n");

                    }
                    if ("INDEX".equals(objectName)) {
                        fwI.append(ddlStr).append("\r\n");

                    }


                }
                fw.write("---创表脚本---\r\n");
                fw.write(fwT.toString());
                fw.write("---序列脚本---\r\n");
                fw.write(fwS.toString());
                fw.write("---索引脚本---\r\n");
                fw.write(fwI.toString());
                fw.flush();
                fw.close();
                DatabaseMetaData dbmd = con.getMetaData();
                /*getTables获取表对象时用户名必须为大写*/
                String dbUserNameStr = jdbcUserName.toUpperCase();
                rs = dbmd.getTables("null", dbUserNameStr, "%", new String[]{"TABLE"});
                List<String> tableNames = new ArrayList<>();
                while (rs.next()) {
                    String tableName = rs.getString("TABLE_NAME");
                    tableNames.add(tableName);
                }
                if (tableNames != null && tableNames.size() > 0) {
                    totalCount=tableNames.size();
                    dataFile = new File(uploadProperties.getBasePath() + dataStr);
                    if (!dataFile.exists()) {
                        /* 创建文件*/
                        dataFile.createNewFile();
                    }
                    FileWriter dataFw = new FileWriter(dataFile, true);
                    for (String str : tableNames) {
                        curcount++;
                        StringBuilder datWt = new StringBuilder();
                        try {
                            String insertSqlStr = SqlUtils.genOracleInsertInto(str, "", statement);
                            dataFw.write(insertSqlStr);

                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                        //导出的进度条信息
                        //将计算出来的数转换成double
                        double dPercent = (double) curcount / totalCount;
                        //再乘上100取整
                        int percent = (int) (dPercent * 100);
                        if (request != null) {
                            request.getSession().setAttribute("curCount", curcount);
                            //比如这里是50
                            request.getSession().setAttribute("percent", percent);
                            //这里是50%
                            request.getSession().setAttribute("percentText", percent + "%");
                        }
                    }
                    dataFw.flush();
                    dataFw.close();
                }
                rs.close();
                statement.close();
                con.close();
                /** 压缩方法2  */
                List<File> fileList = new ArrayList<>();
                if (dataFile != null) {
                    fileList.add(dataFile);
                }
                if (outFile != null) {
                    fileList.add(outFile);
                }
                FileOutputStream fos2 = new FileOutputStream(new File(uploadProperties.getBasePath() + f.format(date) + "oracle数据" + ".zip"));
                try {
                    FileHelperUtils.toZip(fileList, fos2);
                    dataFile.delete();
                    outFile.delete();
                    fos2.close();
                } catch (Exception ex) {
                    logger.error("压缩mysql脚本失败" + ex.toString());
                    return "";
                }
                return uploadProperties.getBasePath() + f.format(date) + "oracle数据" + ".zip";
            }
            if (jdbcDriver.contains("mysql")) {

                File dataFile = null;
                Class.forName(jdbcDriver);
                Connection con = DriverManager.getConnection(jdbcUrl, jdbcUserName, jdbcPassword);

                Statement statement = con.createStatement();

                DatabaseMetaData dbmd = con.getMetaData();

                String dbName = "";
                Integer index = jdbcUrl.lastIndexOf("/");
                String before = jdbcUrl.substring(0, index);
                String after = jdbcUrl.substring(index + 1);
                if (after.contains("?")) {
                    index = after.lastIndexOf("?");
                    dbName = after.substring(0, index);
                }
                con.getMetaData().getDatabaseProductName();
                ResultSet rs = dbmd.getTables(dbName, null, null, new String[]{"TABLE"});
                List<String> tableNames = new ArrayList<>();
                while (rs.next()) {
                    String tableName = rs.getString("TABLE_NAME");
                    tableNames.add(tableName);
                }
                if (tableNames != null && tableNames.size() > 0) {
                    totalCount=tableNames.size();
                    dataFile = new File(uploadProperties.getBasePath() + f.format(date) + "mysql数据" + ".sql");
                    if (!dataFile.exists()) {
                        /* 创建文件*/
                        dataFile.createNewFile();
                    }
                    FileWriter dataFw = new FileWriter(dataFile, true);
                    for (String str : tableNames) {
                        StringBuilder datWt = new StringBuilder();
                        try {
                            String insertSqlStr = SqlUtils.getMysqlInsertSql(statement, str, "");
                            dataFw.write(insertSqlStr);

                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                        //导出的进度条信息
                        //将计算出来的数转换成double
                        double dPercent=(double)curcount/totalCount;
                        //再乘上100取整
                        int  percent=(int)(dPercent*100);
                        if (request != null) {
                            request.getSession().setAttribute("curCount", curcount);
                            //比如这里是50
                            request.getSession().setAttribute("percent", percent);
                            //这里是50%
                            request.getSession().setAttribute("percentText", percent + "%");
                        }
                    }
                    dataFw.flush();
                    dataFw.close();
                }
                rs.close();
                statement.close();
                con.close();
                /** 压缩方法2  */
                List<File> fileList = new ArrayList<>();
                if (dataFile != null) {
                    fileList.add(dataFile);
                }
                FileOutputStream fos2 = new FileOutputStream(new File(uploadProperties.getBasePath() + f.format(date) + "mysql数据" + ".zip"));
                try {
                    FileHelperUtils.toZip(fileList, fos2);
                    dataFile.delete();
                    fos2.close();
                } catch (Exception ex) {
                    logger.error("压缩mysql脚本失败" + ex.toString());
                    return "";
                }
                return uploadProperties.getBasePath() + f.format(date) + "mysql数据" + ".zip";
            }else if(jdbcDriver.contains("postgresql")){
                File dataFile = null;
                Class.forName(jdbcDriver);
                Connection con = DriverManager.getConnection(jdbcUrl, jdbcUserName, jdbcPassword);

                Statement statement = con.createStatement();

                DatabaseMetaData dbmd = con.getMetaData();

                String dbName = "";
                Integer index = jdbcUrl.lastIndexOf("/");
                String before = jdbcUrl.substring(0, index);
                String after = jdbcUrl.substring(index + 1);
                if (after.contains("?")) {
                    index = after.lastIndexOf("?");
                    dbName = after.substring(0, index);
                }else{
                    dbName=after;
                }
                con.getMetaData().getDatabaseProductName();
                ResultSet rs = dbmd.getTables(dbName, null, null, new String[]{"TABLE"});
                List<String> tableNames = new ArrayList<>();
                while (rs.next()) {
                    String tableName = rs.getString("TABLE_NAME");
                    tableNames.add(tableName);
                }
                if (tableNames != null && tableNames.size() > 0) {
                    totalCount=tableNames.size();
                    dataFile = new File(uploadProperties.getBasePath() + f.format(date) + "postgresql数据" + ".sql");
                    if (!dataFile.exists()) {
                        /* 创建文件*/
                        dataFile.createNewFile();
                    }
                    FileWriter dataFw = new FileWriter(dataFile, true);
                    for (String str : tableNames) {
                        StringBuilder datWt = new StringBuilder();
                        try {
                            String insertSqlStr = SqlUtils.getPostgresInsertSql(statement, str, "");
                            dataFw.write(insertSqlStr);

                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                        //导出的进度条信息
                        //将计算出来的数转换成double
                        double dPercent=(double)curcount/totalCount;
                        //再乘上100取整
                        int  percent=(int)(dPercent*100);
                        if (request != null) {
                            request.getSession().setAttribute("curCount", curcount);
                            //比如这里是50
                            request.getSession().setAttribute("percent", percent);
                            //这里是50%
                            request.getSession().setAttribute("percentText", percent + "%");
                        }
                    }
                    dataFw.flush();
                    dataFw.close();
                }
                rs.close();
                statement.close();
                con.close();
                /** 压缩方法2  */
                List<File> fileList = new ArrayList<>();
                if (dataFile != null) {
                    fileList.add(dataFile);
                }
                FileOutputStream fos2 = new FileOutputStream(new File(uploadProperties.getBasePath() + f.format(date) + "postgresql数据" + ".zip"));
                try {
                    FileHelperUtils.toZip(fileList, fos2);
                    dataFile.delete();
                    fos2.close();
                } catch (Exception ex) {
                    logger.error("压缩postgresql脚本失败" + ex.toString());
                    return "";
                }
                return uploadProperties.getBasePath() + f.format(date) + "postgresql数据" + ".zip";
            }
        }finally {
            lock.unlock();
        }
        return "";
    }

    @Override
    public Integer queryByName(String name, BackUpDo backUpDo) {
        Wrapper<BackUpDo> backUpDoEntityWrapper=new EntityWrapper<>();
        backUpDoEntityWrapper.eq("job_name",name);
        if(backUpDo!=null){
            backUpDoEntityWrapper.ne("id",backUpDo.getId());
        }
        return baseMapper.selectCount(backUpDoEntityWrapper);
    }

    @Override
    public void downloadFile(HttpServletResponse response, BackUpDo backUpDo) {
       BufferedInputStream bis = null;
        FileInputStream fis = null;
        try {
            if (backUpDo != null && StringUtils.isNotBlank(backUpDo.getBackUrl())) {
                File file = new File(backUpDo.getBackUrl());
                if (file != null && file.exists()) {
                    // 以流的形式下载文件。
                    fis = new FileInputStream(file);
                    response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(file.getName(), "UTF-8"));
                    /*                response.setHeader("Content-Disposition", "attachment; filename=" + file.getName());*/
                    IOUtils.copy(fis, response.getOutputStream());
                    response.flushBuffer();

                } else {
                    ((HttpServletResponse) response).setStatus(HttpStatus.OK.value());
                    try {
                        JSONObject jsonObject = new JSONObject();
                        jsonObject.put("code", 402);
                        jsonObject.put("msg", "备份文件不存在");
                        response.setContentType("text/html;charset=UTF-8");
                        response.getWriter().print(jsonObject);
                    } catch (IOException e) {
                        logger.error(e.getMessage(), e);
                    }
                }
            } else {
                ((HttpServletResponse) response).setStatus(HttpStatus.OK.value());
                try {
                    JSONObject jsonObject = new JSONObject();
                    jsonObject.put("code", 402);
                    jsonObject.put("msg", "文件已经被删除");
                    response.setContentType("text/html;charset=UTF-8");
                    response.getWriter().print(jsonObject);
                } catch (IOException e) {
                    logger.error(e.getMessage(), e);
                }
            }
        }catch (Exception ex){
            logger.error("下载出错了"+ex.toString());
        }
    }

    @Override
    public void setBackUpData(BackUpDo backUpDo, String backUrl) {
        Integer index=backUrl.lastIndexOf("/");
        String before=backUrl.substring(0,index);
        String after=backUrl.substring(index+1);
        backUpDo.setName(after);
        FileInputStream fis = null;
        try {

            //获取文件大小
            File f = new File(backUrl);
            fis = new FileInputStream(f);
            /*单位KB*/
            Integer available = fis.available();
            backUpDo.setSizeNum(available);
        } catch (IOException e) {
            backUpDo.setSizeNum(0);
        } finally {
            if (null != fis) {
                try {
                    fis.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }


}


